import pandas as pd
from utils import is_pure_punctuation


def preprocess_excel(file_path):
    """预处理Excel文件，分离纯符号和文本内容"""
    xls = pd.ExcelFile(file_path)
    sheet_names = xls.sheet_names[1:]  # 跳过第一个sheet

    all_rows = []
    symbol_rows = []
    text_rows = []
    row_counter = 1

    for sheet_name in sheet_names:
        df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)

        for idx, row in df.iterrows():
            if pd.notna(row[1]) and row[1] != "":
                eng_content = str(row[2]) if pd.notna(row[2]) else ""
                chi_content = str(row[3]) if pd.notna(row[3]) else ""

                if eng_content == "英文内容" and chi_content == "中文内容":
                    continue

                record = {
                    "原始行号": idx + 1,
                    "全局行号": row_counter,
                    "来源表格": sheet_name,
                    "英文内容": eng_content,
                    "中文内容": chi_content
                }

                eng_invalid = is_pure_punctuation(eng_content)
                chi_invalid = is_pure_punctuation(chi_content)

                if eng_invalid or chi_invalid:
                    if eng_invalid and chi_invalid:
                        record["分类原因"] = "中英文均为标点"
                    elif eng_invalid:
                        record["分类原因"] = "英文标点"
                    else:
                        record["分类原因"] = "中文标点"
                    symbol_rows.append(record)
                else:
                    text_rows.append(record)

                all_rows.append(record)
                row_counter += 1

    full_df = pd.DataFrame(all_rows)
    symbol_df = pd.DataFrame(symbol_rows)
    text_df = pd.DataFrame(text_rows)

    return full_df, symbol_df, text_df